﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Collections;

namespace INSA
{
    class DBUtil
    {
        private static OleDbConnection oleCon = new OleDbConnection();

        public static void oleConnect()
        {
            try
            {
                //MDB와 연결
                DBUtil.oleCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Config.Path + "\\INSA.mdb";                
                DBUtil.oleCon.Open();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        public static void oleClose()
        {
            //현재 MDB와 연결되어 있으면 연결을 해제한다.
            if (DBUtil.oleCon.State != ConnectionState.Closed)
            {
                DBUtil.oleCon.Close();
            }
        }

        //과목 그룹 목록 불러오기
        public static ArrayList getSubjectGroupList()
        {
            ArrayList list = new ArrayList();
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            try
            {
                if (DBUtil.oleCon.State == ConnectionState.Open)
                {

                    cmd = new OleDbCommand("SELECT SUBJECT_GROUP FROM SUBJECTINFO GROUP BY SUBJECT_GROUP", DBUtil.oleCon);
                    read = cmd.ExecuteReader();

                    while (read.Read())
                    {
                        list.Add(read["SUBJECT_GROUP"]);
                    }

                }
                else
                {
                    MessageBox.Show("MDB연결이 끊겼습니다.   ", "오류");
                    return null;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }           

            if (read.IsClosed == false)
            {
                read.Close();
            }

            return list;
        }

        //지정된 과목 그룹에 대한 과목 불러오기
        public static ArrayList getSubject(string subject_group)
        {
            ArrayList list = new ArrayList();
            OleDbCommand cmd = null;
            OleDbDataReader read = null; 
            
            try
            {

                cmd = new OleDbCommand("SELECT SUBJECT_NAME FROM SUBJECTINFO WHERE SUBJECT_GROUP='" + subject_group + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                
                while (read.Read())
                {
                    list.Add(read["SUBJECT_NAME"]);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message,"오류");
            }

            if (read.IsClosed == false)
            {
                read.Close();
            }

            return list;
        }

        //특정 과목의 과목 그룹 찾기
        public static string getSubjectGroup(string subject)
        {

            string subject_group = null;
            OleDbCommand cmd = null;

            try
            {

                cmd = new OleDbCommand("SELECT SUBJECT_GROUP FROM SUBJECTINFO WHERE SUBJECT_NAME='"+subject+"'", DBUtil.oleCon);                
                subject_group = ((string)cmd.ExecuteScalar()).Length>0?(string)cmd.ExecuteScalar():"";

            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message + "\\n\\n" + ex.StackTrace);

            }

            return subject_group;

        }

        //소속 기관 검색
        public static ArrayList getOrganList(string organ)
        {
            ArrayList list = new ArrayList();
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            try
            {

                cmd = new OleDbCommand("SELECT NAME FROM ORGANTABLE WHERE NAME like '%" + organ + "%'", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    list.Add(read["NAME"]);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "오류");
            }

            if (read.IsClosed == false)
            {
                read.Close();
            }

            return list;
        }

        //소속 기관명 -> 소속 기관 코드
        public static string getOrganCode(string organ_name) 
        {
            OleDbCommand cmd = null;

            try
            {
                cmd = new OleDbCommand("SELECT CODE FROM ORGANTABLE WHERE NAME='" + organ_name + "'", DBUtil.oleCon);
                return (string)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "오류");
            }

            return "";
        }

        //소속 기관 코드 -> 소속 기관명
       public static string getOrganName(string organ_code) {

           OleDbCommand cmd = null;

           try
           {
               cmd = new OleDbCommand("SELECT NAME FROM ORGANTABLE WHERE CODE='" + organ_code + "'", DBUtil.oleCon);
               return (string)cmd.ExecuteScalar();
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message, "오류");
           }

           return "";
       }


        //다명평가자 정보 불러오기
       public static ArrayList getValuerInfo()
       {
           ArrayList data = new ArrayList();
           OleDbCommand cmd = null;
           OleDbDataReader read = null;
           bool bHasRows = false;

           try
           {
               cmd = new OleDbCommand("SELECT * FROM USERS", DBUtil.oleCon);
               read = cmd.ExecuteReader();

               if (read.HasRows)
               {
                   read.Read();
                   for (int i = 0; i < read.FieldCount; i++)
                   {
                       data.Add(read.GetValue(i));
                   }
                   bHasRows = true;
               }
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message, "오류");
           }
           finally
           {

               if (read.IsClosed == false)
               {
                   read.Close();
               }

           }


           return bHasRows?data:null;
       }

        //다면평가자 정보 DB에 레코드 삽입
        public static bool insertValuerInfo(ModelValuerInfo mvi){

            OleDbCommand cmd = null;
            string sql = "";
            int affectedRows = 0;

            try
            {
                sql = "INSERT INTO USERS (USER_CODE,USER_NAME,USER_SUBJECT,ORGAN_CODE,USER_PW,REGTIME) VALUES ('"+mvi.Pno+"','"+mvi.Name+"','"+mvi.Subject+"','"+mvi.Organ+"','"+mvi.Passwd+"','"+mvi.Regtime+"')";                
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                affectedRows = cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message+"\n\n"+ex.StackTrace, "오류");
            }

            return affectedRows==1?true:false;
        }

        //다면평가자 정보 DB의 레코드 수정
        public static bool updateValuerInfo(ModelValuerInfo mvi)
        {

            OleDbCommand cmd = null;
            string sql = "";
            int affectedRows = 0;

            try
            {
                sql = "UPDATE USERS SET "
                + "USER_CODE='"+mvi.Pno + "',"
                + "USER_NAME='"+mvi.Name + "',"
                + "USER_SUBJECT='"+mvi.Subject + "',"
                + "ORGAN_CODE='"+mvi.Organ + "',"
                + "USER_PW='"+mvi.Passwd + "',"
                + "REGTIME='"+mvi.Regtime + "' WHERE INX="+mvi.Inx;
                //MessageBox.Show(sql);
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                affectedRows = cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "오류");
            }

            return affectedRows == 1 ? true : false;
        }

        //현재 사용자 성명
        public static string getCurrentUserName()
        {
            OleDbCommand cmd = null;
            string user_name = "";
            try
            {
                cmd = new OleDbCommand("SELECT USER_NAME FROM USERS", DBUtil.oleCon);
                user_name = (string)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "오류");
            }


            return user_name;
        }

        //사용자 정보 등록 여부
        public static bool isRegistedUserInfo()
        {

            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            
            bool isRegisted = false;

            try
            {
                cmd = new OleDbCommand("SELECT * FROM USERS", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                isRegisted = read.HasRows;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "오류");
            }


            return isRegisted;

        }


        public static bool updatePassWord(int inx, string p)
        {
            OleDbCommand cmd = null;

            try
            {
                cmd = new OleDbCommand("UPDATE USERS SET USER_PW='"+p+"' WHERE INX=" + inx, DBUtil.oleCon);
                if (cmd.ExecuteNonQuery() == 1)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message + "\n\n" + ex.StackTrace);

            }
            return false;

        }
        
        //패스워드 확인(inx)
        public static bool checkPassword(int inx, string p)
        {
            OleDbCommand cmd = null;
            string password = "";
            try
            {
                cmd = new OleDbCommand("SELECT USER_PW FROM USERS WHERE INX="+inx,DBUtil.oleCon);
                password = (string)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n\n" + ex.StackTrace);
            }

            if (password.Equals(p))
            {
                return true;
            }            
            else
            {
                return false;
            }
        }

        //패스워드 확인(inx없음)
        public static bool checkPassword(string p)
        {
            OleDbCommand cmd = null;
            string password = "";
            try
            {
                cmd = new OleDbCommand("SELECT USER_PW FROM USERS", DBUtil.oleCon);
                password = (string)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n\n" + ex.StackTrace);
            }

            if (password.Equals(p))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //다면평가정보 추출
        public static ArrayList getMultipleInformations(byte targetType)
        {

            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            ArrayList list = new ArrayList();

            try
            {

                cmd = new OleDbCommand("SELECT A.* FROM MULTITABLE A, TARGETS B WHERE A.M_PCODE=B.TARGET_PCODE AND B.TARGET_TYPE="  + targetType + " ORDER BY A.M_PRANK ASC", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                while (read.Read())
                {
                    ArrayList tmp = new ArrayList();
                    tmp.Add(read.GetValue(2));  //M_PCODE
                    tmp.Add(read.GetValue(4));  //M_ITEM1
                    tmp.Add(read.GetValue(5));  //M_ITEM2
                    tmp.Add(read.GetValue(6));  //M_ITEM3
                    tmp.Add(read.GetValue(7));  //M_ITEM4
                    tmp.Add(read.GetValue(8));  //M_ITEM5
                    tmp.Add(read.GetValue(9));  //TOTAL_POINT
                    tmp.Add(read.GetValue(10));  //REGTIME

                    list.Add(tmp);
                }

            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message, "오류");

            }

            return list;

        }

        //다면평가를 한 적이 있는지 여부
        public static bool existMTargetValue()
        {
            OleDbCommand cmd = null;
            int cnt = 0;

            try
            {

                cmd = new OleDbCommand("SELECT COUNT(*) FROM MULTITABLE", DBUtil.oleCon);
                cnt = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "오류");
            }

            return cnt == 0 ? false : true;
        }

        //평가대상자 이름 구하기
        public static string getTargetName(string pcode)
        {
            OleDbCommand cmd = null;
            string name = "";

            try
            {

                cmd = new OleDbCommand("SELECT TARGET_NAME FROM TARGETS WHERE TARGET_PCODE='" + pcode + "'", DBUtil.oleCon);
                name = (string)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "오류");
            }

            return name;
        }


        public static void insertMTargetInfo(ArrayList list)
        {
            OleDbCommand cmd = null;
            string sql = "INSERT INTO TARGETS (TARGET_TYPE,ORGAN_CODE,TARGET_PCODE,TARGET_NAME,TARGET_SEX,TARGET_SUBJECT) VALUES (" +
               list[1] + ",'" +
                Config.Mvi.Organ + "','" +
               (string)list[2] + "','" +
               (string)list[3] + "','" +
               (string)list[4] + "','" +
               (string)list[5] + "')";
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "오류");
            }
            
        }

        public static void truncateTarget(int targetType)
        {
            OleDbCommand cmd = null;
            try
            {
                cmd = new OleDbCommand("DELETE FROM TARGETS WHERE TARGET_TYPE=" + targetType, DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "오류");
            }
        }

        public static ArrayList getMTargetInfo(byte targetType)
        {
            //TARGETS 테이블에서 개인번호, 성명을 불러와 2차원 배열에 입력 후 반환

            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList list = new ArrayList();

            string sql = "SELECT TARGET_PCODE, TARGET_NAME, TARGET_SUBJECT FROM TARGETS WHERE TARGET_TYPE=" + targetType + " ORDER BY INX ASC";
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                read = cmd.ExecuteReader();
                
                while (read.Read())
                {
                    ArrayList data = new ArrayList();
                    data.Add(read.GetValue(0));
                    data.Add(read.GetValue(1));
                    data.Add(read.GetValue(2));

                    list.Add(data);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show("public static ArrayList getMTargetInfo() : \n\n" + ex.Message, "오류");
            }

            return list;
            
        }

        public static ArrayList getMTargetInfo(string pcode)
        {
            //TARGETS 테이블에서 개인번호, 성명을 불러와 2차원 배열에 입력 후 반환

            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList list = new ArrayList();

            string sql = "SELECT TARGET_PCODE, TARGET_NAME, TARGET_SUBJECT FROM TARGETS WHERE TARGET_PCODE='" + pcode + "'";
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                read = cmd.ExecuteReader();

                if (read.Read())
                {
                    list.Add(read.GetValue(0));
                    list.Add(read.GetValue(1));
                    list.Add(read.GetValue(2));
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n\n" + ex.StackTrace, "오류");
            }

            return list;

        }

        public static ArrayList getMultiTargetInfo()
        {
            //TARGETS 테이블에서 개인번호, 성명을 불러와 2차원 배열에 입력 후 반환

            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList list = new ArrayList();

            string sql = "SELECT M_PCODE FROM MULTITABLE ORDER BY M_PRANK ASC";
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    ArrayList data = new ArrayList();
                    data.Add(read.GetValue(0));

                    list.Add(data);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show("public static ArrayList getMTargetInfo() : \n\n" + ex.Message, "오류");
            }

            return list;

        }

        

        //다면평가대상자 전체 인원
        public static int getNumOfTagets(int targetType)
        {
            OleDbCommand cmd = null;
            int numOfTagets = 0;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM TARGETS WHERE TARGET_TYPE=" + targetType, DBUtil.oleCon);
                numOfTagets = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return numOfTagets;
        }

        public static void setInitTargetes(int targetType)
        {
            OleDbCommand cmd = null;
            try
            {
                cmd = new OleDbCommand("DELETE FROM TARGETS WHERE TARGET_TYPE=" + targetType, DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show("public static void setInitTargetes()\n\n" + ex.Message, "오류");
            }
        }

        public static ArrayList getRatioNum(int numOfTargets)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {
                //[주의]다면평가에서 본인은 평사대상자에서 제외 되므로 Config.NumOfTargets 에서 1을 뺀 값을 사용한다.
                cmd = new OleDbCommand("SELECT S_SU,S_WO,S_MI,S_YA FROM WRATIO_TABLE WHERE S_NUM=" + (numOfTargets - 1), DBUtil.oleCon);
                read = cmd.ExecuteReader();
                if (read.Read())
                {
                    data.Add(read.GetValue(0));
                    data.Add(read.GetValue(1));
                    data.Add(read.GetValue(2));
                    data.Add(read.GetValue(3));

                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n\n" + ex.StackTrace, "오류");
            }
            return data;
        }

        public static ArrayList getTotalSu(Int32 num)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT D_RECORD FROM DISTRIBUTION_TABLE WHERE D_GRADE='수' AND D_NUMBER=" + num + " ORDER BY D_SERIAL ASC", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                
                while(read.Read()){
                    data.Add((float)read.GetValue(0));
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show("public static int[] getRatioNum()\n\n" + ex.Message + "\n\n" + ex.StackTrace, "오류");
            }
            return data;
        }

        public static ArrayList getTotalWo(Int32 num)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT D_RECORD FROM DISTRIBUTION_TABLE WHERE D_GRADE='우' AND D_NUMBER=" + num + " ORDER BY D_SERIAL ASC", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                
                while (read.Read())
                {
                    data.Add((float)read.GetValue(0));
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show("public static int[] getRatioNum()\n\n" + ex.Message + "\n\n" + ex.StackTrace, "오류");
            }
            return data;
        }

        public static ArrayList getTotalMi(Int32 num)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT D_RECORD FROM DISTRIBUTION_TABLE WHERE D_GRADE='미' AND D_NUMBER=" + num + " ORDER BY D_SERIAL ASC", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    data.Add((float)read.GetValue(0));
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n\n" + ex.StackTrace, "오류");
            }
            return data;
        }

        public static void insertMultiInfo(ArrayList arr)
        {
            OleDbCommand cmd = null;
            try
            {
                string sql = "INSERT INTO MULTITABLE (M_PRANK,M_PCODE,M_VALUER,M_ITEM1,M_ITEM2,M_ITEM3,M_ITEM4,M_ITEM5,TOTAL_POINT,REGTIME) VALUES (" + 
                arr[0] + ",'" +
                arr[1] + "','" +
                arr[2] + "'," +
                arr[3] + "," +
                arr[4] + "," +
                arr[5] + "," +
                arr[6] + "," +
                arr[7] + "," +
                arr[8] + ",'" +
                arr[9] + "')";
            
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "오류");
            }
            
        }

        public static void setInitMultiInfo(int targetType)
        {
            OleDbCommand cmd = null;
            try
            {
                cmd = new OleDbCommand("DELETE FROM MULTITABLE WHERE M_PCODE IN (SELECT TARGET_PCODE FROM TARGETS WHERE TARGET_TYPE=" + targetType + ")", DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show("[" + ex.Source + "] " + ex.Message, "오류");
            } 
        }

        public static ArrayList getMTargetDetailInfo(string p)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            ArrayList list = new ArrayList();

            try
            {
                cmd = new OleDbCommand("SELECT M_ITEM1,M_ITEM2,M_ITEM3,M_ITEM4,M_ITEM5,TOTAL_POINT FROM MULTITABLE WHERE M_PCODE='" + p + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                if (read.Read())
                {
                    list.Add(read.GetValue(0));
                    list.Add(read.GetValue(1));
                    list.Add(read.GetValue(2));
                    list.Add(read.GetValue(3));
                    list.Add(read.GetValue(4));
                    list.Add(read.GetValue(5));

                }
                    
            }catch(Exception ex){

                MessageBox.Show(ex.Message, "오류");

            }

            return list;

        }

        public static bool isExistedMultiInfo(object p)
        {
            OleDbCommand cmd = null;
            bool isExisted = false;
            int cnt = 0;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM MULTITABLE WHERE M_PCODE='" + p.ToString() + "'", DBUtil.oleCon);
                cnt = (int)cmd.ExecuteScalar();
                if(cnt != 0 ){
                    isExisted = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "오류");
            }
            return isExisted;
        }

        public static bool isExistedMultiList(byte targetType)
        {
            OleDbCommand cmd = null;
            bool isExisted = false;
            int cnt = 0;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM MULTITABLE A, TARGETS B WHERE A.M_PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType, DBUtil.oleCon);
                cnt = (int)cmd.ExecuteScalar();
                if (cnt != 0)
                {
                    isExisted = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n\n" + ex.StackTrace, "오류", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return isExisted;
        }

        public static int countMultiInfo(byte targetType)
        {
            OleDbCommand cmd = null;
            
            int cnt = 0;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM MULTITABLE A, TARGETS B WHERE A.M_PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType, DBUtil.oleCon);
                cnt = (int)cmd.ExecuteScalar();                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n\n" + ex.StackTrace, "오류", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return cnt;
        }

        public static int countMTarget(byte targetType)
        {
            OleDbCommand cmd = null;

            int cnt = 0;

            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM TARGETS WHERE TARGET_TYPE=" + targetType, DBUtil.oleCon);
                cnt = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n\n" + ex.StackTrace, "오류", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return cnt;
        }

        public static void updateMultiInfo(ArrayList arr)
        {
            OleDbCommand cmd = null;
            try
            {
                string sql = "UPDATE MULTITABLE SET M_PRANK=" + arr[0] + ", M_VALUER='" + arr[2] + "',M_ITEM1=" + arr[3] + ",M_ITEM2=" + arr[4] + ",M_ITEM3=" + arr[5] + ",M_ITEM4=" + arr[6] + ",M_ITEM5=" + arr[7] + ",TOTAL_POINT=" + arr[8] + ",REGTIME='" + arr[9] + "' WHERE M_PCODE='" + arr[1] +"'";

                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show("[" + ex.Source + "]" + ex.Message + "\n\n" + ex.StackTrace, "오류");
            }
        }

        public static void updateMultiItem(ArrayList arr)
        {
            OleDbCommand cmd = null;
            string sql = "UPDATE MULTITABLE SET M_ITEM1=" + arr[1] + ", M_ITEM2=" + arr[2] + ", M_ITEM3=" + arr[3] + ", M_ITEM4=" + arr[4] + ", M_ITEM5=" + arr[5] + " WHERE M_PCODE='" + arr[0] + "'";
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(sql + "\n\n"  + ex.Message, "오류");
            }
        }

        public static ArrayList getTargetListFromMultiTable(byte targetType)
        {
            
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList list = new ArrayList();

            string sql = "SELECT A.M_PCODE, B.TARGET_NAME, B.TARGET_SUBJECT FROM MULTITABLE A, TARGETS B WHERE A.M_PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType + " ORDER BY A.M_PRANK ASC";
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    ArrayList data = new ArrayList();
                    data.Add(read.GetValue(0));
                    data.Add(read.GetValue(1)); 
                    data.Add(read.GetValue(2));

                    list.Add(data);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show("public static ArrayList getMTargetInfo() : \n\n" + ex.Message, "오류");
            }

            return list;
        }



        public static void createTable(string sql)
        {
            OleDbCommand cmd = null;
            try
            {
                cmd = new OleDbCommand(sql, oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n\n" + ex.StackTrace, "오류");
            }
        }

        ///
        /// 동명이인확인: getSameNameExist(string name)
        ///
        public static bool getSameNameExist(string name)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM TARGETS WHERE TARGET_NAME='" + name + "'", oleCon);
                if ((int)cmd.ExecuteScalar() > 1)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n\n" + ex.StackTrace, "오류");
            }
            return b;
        }
    }
}
